package com.hy.dao.impl;
/*
 * @ Date: 2024/5/14 22:32
 * @ Description: OutstoretableDaoImpl出库
 **/

import com.hy.bean.Outstoretable;
import com.hy.dao.OutstoretableDao;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import com.hy.vo.OutsotreVo;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.Field;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class OutstoretableDaoImpl implements OutstoretableDao {

    private final QueryRunner queryRunner = new QueryRunner();
    BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());

    @Override
    public Outstoretable queryById(Integer id) {
        return null;
    }

    @Override
    public List<OutsotreVo> queryAllByLimit(Integer currPage, Integer pageSize, Outstoretable outstoretable) {
        Connection connection = null;
        String sql = "with t1 as(\n" +
                "select\n" +
                "\tDISTINCT\n" +
                "    store.id storeId,\n" +
                "\t\tstore.outStoreNumber StoreNumber,\n" +
                "\t\tstore.outStorePerson outStorePerson,\n" +
                "\t\temp.employeeName employeeName,\n" +
                "\t\tcar.partName partName,\n" +
                "\t\tcar.partUnit partUnit,\n" +
                "\t\tsotreOrder.outStoreNumber outStoreNumber,\n" +
                "\t\tproceduretb.producerName producerName,\n" +
                "\t\tcar.costPrice costPrice,\n" +
                "\t\tround(sotreOrder.outStoreNumber * salePrice,3) zje,\n" +
                "\t\tstore.outStoreTime outStoreTime,\n" +
                "\t\tstore.orderTable_id orderTableId\n" +
                "from outstoretable store -- 出库表\n" +
                "\t\tinner join employeetable emp on store.outStorePerson = emp.id\n" +
                "\t\t\n" +
                "    inner join outsotre_ordertable sotreOrder on store.id = sotreOrder.outstoreTable_id -- 出库中间表\n" +
                "\t\t\n" +
                "    inner join carpartstable car on sotreOrder.partTable_id = car.id -- 汽车零件表\n" +
                "\t\t\n" +
                "    inner join producertable proceduretb on car.producerTable_id = proceduretb.id --  供应商表\n" +
                "\t\t),\n" +
                "\t\t t2 as (\n" +
                "\t\t\tselect ordersParts.ordersTable_id orderTableId,min(ordersParts.orderCount) orderCountMin from orders_partsTable ordersParts GROUP BY 1 order by 1\t-- 订单中间表\n" +
                "\t\t)\n" +
                "\t\t\n" +
                "\t\tselect t1.storeId id,\n" +
                "\t\tt1.employeeName employeeName,\n" +
                "\t\tt1.partName partName,\n" +
                "\t\tpartUnit partUnit,\n" +
                "\t\tt1.outStoreNumber outStoreNumber,\n" +
                "\t\tt1.producerName producerName,\n" +
                "\t\tt1.costPrice costPrice,\n" +
                "\t\tt1.zje zje,\n" +
                "\t\tt1.outStoreTime outStoreTime,\n" +
                "\t\tt1.StoreNumber StoreNumber,\n" +
                "\t\tt2.orderCountMin  orderCountMin\n" +
                "\t\tfrom t1 join t2 on t1.orderTableId =  t2.orderTableId where 1=1";
        try {
            connection = DBManager.getConnection();
            Map<String, Object> sql1 = createSql(outstoretable, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
            return queryRunner.query(connection, sql2, new BeanListHandler<>(OutsotreVo.class,basicRowProcessor), (currPage - 1) * pageSize, pageSize);
        } catch (Exception e) {
            throw new BizException("Dao异常",e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private Map<String, Object> createSql(Outstoretable outstoretable, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (outstoretable.getOutstoreperson() != null)   {
            map.put("outstoreperson", outstoretable.getOutstoreperson());
        } if (outstoretable.getOutstoretime() != null)  {
            map.put("outstoretime", outstoretable.getOutstoretime());
        } if (outstoretable.getOutstorenumber() != null)    {
            map.put("outstorenumber", outstoretable.getOutstorenumber());
        }
        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = outstoretable.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                s2 = map.get(declaredField.getName()).toString();
                if (s.equals("outstoretime")){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = new Date(s2);
                    s2 = sdf.format(date);
                } else if (s.equals("outstorenumber")) {
                    s = "StoreNumber";
                }
                sql += " and t1." + s + "= '" + s2 + "'";
            }
        }
        sql += " limit ?,?";
        mapsql.put("sql", sql);
        return mapsql;
    }


    @Override
    public long count(Outstoretable outstoretable) {
        Connection connection = null;
        String sql = "with t1 as(\n" +
                "select\n" +
                "\tDISTINCT\n" +
                "    store.id storeId,\n" +
                "\t\tstore.outStoreNumber StoreNumber,\n" +
                "\t\tstore.outStorePerson outStorePerson,\n" +
                "\t\temp.employeeName employeeName,\n" +
                "\t\tcar.partName partName,\n" +
                "\t\tcar.partUnit partUnit,\n" +
                "\t\tsotreOrder.outStoreNumber outStoreNumber,\n" +
                "\t\tproceduretb.producerName producerName,\n" +
                "\t\tcar.costPrice costPrice,\n" +
                "\t\tround(sotreOrder.outStoreNumber + salePrice,3) zje,\n" +
                "\t\tstore.outStoreTime outStoreTime,\n" +
                "\t\tstore.orderTable_id orderTableId\n" +
                "from outstoretable store -- 出库表\n" +
                "\t\tinner join employeetable emp on store.outStorePerson = emp.id\n" +
                "\t\t\n" +
                "    inner join outsotre_ordertable sotreOrder on store.id = sotreOrder.outstoreTable_id -- 出库中间表\n" +
                "\t\t\n" +
                "    inner join carpartstable car on sotreOrder.partTable_id = car.id -- 汽车零件表\n" +
                "\t\t\n" +
                "    inner join producertable proceduretb on car.producerTable_id = proceduretb.id --  供应商表\n" +
                "\t\t),\n" +
                "\t\t t2 as (\n" +
                "\t\t\tselect ordersParts.ordersTable_id orderTableId,min(ordersParts.orderCount) orderCountMin from orders_partsTable ordersParts GROUP BY 1 order by 1\t-- 订单中间表\n" +
                "\t\t)\n" +
                "\t\t\n" +
                "\t\tselect count(*)count from t1 join t2 on t1.orderTableId =  t2.orderTableId where 1=1";
        try {
            connection = DBManager.getConnection();
            Map<String, Object> sql1 = createSql2(outstoretable, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
            return queryRunner.query(connection, sql2, new ScalarHandler<>("count"));
        } catch (Exception e) {
            throw new BizException("Dao异常",e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private Map<String, Object> createSql2(Outstoretable outstoretable, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (outstoretable.getOutstoreperson() != null)   {
            map.put("outstoreperson", outstoretable.getOutstorenumber());
        } if (outstoretable.getOutstoretime() != null)  {
            map.put("outstoretime", outstoretable.getOutstoretime());
        } if (outstoretable.getOutstorenumber() != null)    {
            map.put("outstorenumber", outstoretable.getOutstorenumber());
        }
        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = outstoretable.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                s2 = map.get(declaredField.getName()).toString();
                if (s.equals("outstoretime")){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = new Date(s2);
                    s2 = sdf.format(date);
                }
                sql += " and t1." + s + "= '" + s2 + "'";
            }
        }
        mapsql.put("sql", sql);
        return mapsql;
    }

    /***
     * @deprecated 添加出库
     * @param connection 连接
     * @param outstoretable 实例对象
     * @return
     */
    @Override
    public Integer insert(Connection connection, Outstoretable outstoretable) {
        String sql = "INSERT INTO outstoretable (`id`, `outStoreNumber`, `orderTable_id`, `outStorePerson`, `outStoreTime`, `consignee`, `remark`) VALUES (null, ?, ?, ?, ?, ?, ?)";
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            BigInteger insert = queryRunner.insert(connection, sql, new ScalarHandler<>(),
                    outstoretable.getOutstorenumber(),
                    outstoretable.getOrdertableId(),
                    outstoretable.getOutstoreperson(),
                    sdf.format(outstoretable.getOutstoretime()),
                    outstoretable.getConsignee(),
                    outstoretable.getRemark()
            );
            return insert.intValue();

        } catch (SQLException e) {
            throw new BizException("OutstoretableDaoImpl添加异常",e);
        }
    }

    @Override
    public int update(Outstoretable outstoretable) {
        return 0;
    }

    @Override
    public int deleteById(Integer id) {
        return 0;
    }
}
